package DatabaseManager;

/**
 * Database Management Class
 */

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MMBoxManager {
	/**
	 * Properties  
	 */    
	private Connection con;
	private PreparedStatement preparedStmt;
	private ResultSet resultSet;

	private String url;
	private String id;
	private String pw;

	/**
	 * Methods  
	 */
	public MMBoxManager() {
		con = null;
		preparedStmt = null;
		resultSet = null;
		url = "jdbc:mysql://127.0.0.1:3306/MMBOX"; // jdbc:mysql://url:port/'DBname'
		id = "root";
		pw = "wlqwndrydbr";
	}
	/**
	 * Connect DB
	 */
	public void ConnectDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, id, pw);
		} catch (ClassNotFoundException cnte) {
			System.out.println(cnte.getMessage());
		} catch (SQLException se) {
			System.out.println(se.getMessage());
		}
	}
	public void CloseConnectionDB() throws SQLException {
		con.close();
	}

	public void StoreMessage(String[] mmArray, String[] toArray, String[] ccArray, String[] bccArray) {
		String sql = null;
		try {
			sql = "select * from Message_Info where MSGID = (?)";
			preparedStmt = con.prepareStatement(sql);
			preparedStmt.setString(1, mmArray[0]);
			resultSet = preparedStmt.executeQuery();

			if (!resultSet.next())
			{ 
				try {
					sql = "insert into Message_Info values(?,?,?,?,?)";
					preparedStmt = con.prepareStatement(sql);
					preparedStmt.setString(1, mmArray[0]); // MSGID
					preparedStmt.setString(2, mmArray[1]); // Subject
					preparedStmt.setString(3, mmArray[2]); // ContentLocation
					
					preparedStmt.setString(4, mmArray[4]); // Expiry
					preparedStmt.setString(5, mmArray[5]); // Delivery Time
					preparedStmt.executeUpdate();
					//System.out.println("Query1 Success");
				} catch (SQLException se) {
					System.out.println("Query 1" + se.getMessage());
				}
			}
		} catch (SQLException se) {
			System.out.println(se.getMessage());
		}
		sql = null;
		preparedStmt = null;
		//Insert into SenderReceiver_Info Table
		try {
			//System.out.println("Cc = " + toArray.length);
			for (int i = 0; i < toArray.length; i++) {
				sql = "insert into SenderReceiver_Info values(?,?,?,?,?,?,?,?,?,?,?)";
				preparedStmt = con.prepareStatement(sql);
				preparedStmt.setString(1, mmArray[0]); // MSGID
				preparedStmt.setString(2, mmArray[6]); // Sender
				preparedStmt.setString(3, toArray[i]); // Receiver
				preparedStmt.setString(4, mmArray[8]); // SenderDeliveryAllowed
				preparedStmt.setString(5, mmArray[9]); // ReceiverDelivery
				preparedStmt.setString(6, mmArray[10]); // Delivery State
				preparedStmt.setString(7, mmArray[11]); // SenderReadAllowed
				preparedStmt.setString(8, mmArray[12]); // ReceiverReadAllowed
				preparedStmt.setString(9, mmArray[13]); // ReadState
				preparedStmt.setString(10, "To"); // ReceiverState
				preparedStmt.setString(11, mmArray[3]); // Date
				preparedStmt.executeUpdate();
				// //System.out.println("Query2 Success");
			}
		} catch (SQLException se) {
				System.out.println("Query2 to" + se.getMessage());
			// this.UpdateMMBox(mmArray);
		}

		try {
			//System.out.println("Cc = " + ccArray.length);
			for (int i = 0; i < ccArray.length; i++) {
				sql = "insert into SenderReceiver_Info values(?,?,?,?,?,?,?,?,?,?,?)";
				preparedStmt = con.prepareStatement(sql);
				preparedStmt.setString(1, mmArray[0]); // MSGID
				preparedStmt.setString(2, mmArray[6]); // Sender
				preparedStmt.setString(3, ccArray[i]); // Receiver
				preparedStmt.setString(4, mmArray[8]); // SenderDeliveryAllowed
				preparedStmt.setString(5, mmArray[9]); // ReceiverDelivery
				preparedStmt.setString(6, mmArray[10]); // Delivery State
				preparedStmt.setString(7, mmArray[11]); // SenderReadAllowed
				preparedStmt.setString(8, mmArray[12]); // ReceiverReadAllowed
				preparedStmt.setString(9, mmArray[13]); // ReadState
				preparedStmt.setString(10, "Cc"); // ReceiverState
				preparedStmt.setString(11, mmArray[3]); // Date
				preparedStmt.executeUpdate();
				// //System.out.println("Query2 Success");
			}
		} catch (SQLException se) {
			System.out.println("Query2 cc" + se.getMessage());
		}
		try {
			//System.out.println("Cc = " + bccArray.length);
			for (int i = 0; i < bccArray.length; i++) {
				sql = "insert into SenderReceiver_Info values(?,?,?,?,?,?,?,?,?,?,?)";
				preparedStmt = con.prepareStatement(sql);
				preparedStmt.setString(1, mmArray[0]); // MSGID
				preparedStmt.setString(2, mmArray[6]); // Sender
				preparedStmt.setString(3, bccArray[i]); // Receiver
				preparedStmt.setString(4, mmArray[8]); // SenderDeliveryAllowed
				preparedStmt.setString(5, mmArray[9]); // ReceiverDelivery
				preparedStmt.setString(6, mmArray[10]); // Delivery State
				preparedStmt.setString(7, mmArray[11]); // SenderReadAllowed
				preparedStmt.setString(8, mmArray[12]); // ReceiverReadAllowed
				preparedStmt.setString(9, mmArray[13]); // ReadState
				preparedStmt.setString(10, "Bcc"); // ReceiverState
				preparedStmt.setString(11, mmArray[3]); // Date
				preparedStmt.executeUpdate();
				}
		} catch (SQLException se) {
				System.out.println("Query2 bcc" + se.getMessage());
		}
	}

	/**
	 * Update the Database (report, readstate)
	 */
	
	public void UpdateMMBox(String[] mmArray) {
		String sql = null;
		preparedStmt = null;
		try {
			//System.out.println("here is UpdateMMBox");
			sql = "update SenderReceiver_Info set SenderDeliveryAllowed = '"
					+ mmArray[8] + "', " + "ReceiverDeliveryAllowed = '"
					+ mmArray[9] + "', " + "DeliveryState = '" + mmArray[10]
					+ "', " + "SenderReadAllowed = '" + mmArray[11] + "', "
					+ "ReceiverReadAllowed = '" + mmArray[12] + "', "
					+ "ReadState = '" + mmArray[13] + "' " + " where "
					+ "MSGID = '" + mmArray[0] + "' and " + "Sender = '"
					+ mmArray[6] + "' and " + "Receiver = '" + mmArray[7] + "'" + "Date = '" + mmArray[3] + "'";
			// System.out.println("Query is " + sql);

			preparedStmt = con.prepareStatement(sql);
			preparedStmt.executeUpdate();
			// System.out.println("Update Success");

		} catch (SQLException se) {
			System.out.println("UpdateMMBox" + se.getMessage());
		}
	}

	public boolean isExistMsgId(String msgID) throws SQLException {
		String sql = null;
		preparedStmt = null;
		resultSet = null;

		sql = "select * from Message_Info where MSGID = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, msgID);
		resultSet = preparedStmt.executeQuery();

		if (resultSet.next())
			return true;
		else
			return false;
	}

	public String[] getMsgInfo(String msgId) throws SQLException {

		String sql = new String();
		List<String> tempResult = new ArrayList<String>();
		String[] result = new String[5];

		sql = "select * from Message_Info where MSGID = '" + msgId + "'";
		preparedStmt = con.prepareStatement(sql);
		// preparedStmt.setString(1, msgId); //PDU
		resultSet = preparedStmt.executeQuery();
		// System.out.println("MMBox get select complete");

		// System.out.println("msgId = "+msgId);
		if (resultSet.next()) {
			// System.out.println("In get Method kyakya result = " +
			// resultSet.getString(1));
			result[0] = resultSet.getString("MSGID");
			result[1] = resultSet.getString("Subject");
			result[2] = resultSet.getString("ContentLocation");
			//result[3] = resultSet.getString("Date");
			result[3] = resultSet.getString("Expiry");
			result[4] = resultSet.getString("DeliveryTime");
		}

		/*
		 * result[0] = resultSet.getString("MSGID"); result[1] =
		 * resultSet.getString("Subject"); result[2] =
		 * resultSet.getString("ContentLocation"); result[3] =
		 * resultSet.getString("Date"); result[4] =
		 * resultSet.getString("Expiry"); result[5] =
		 * resultSet.getString("DeliveryTime");
		 */

		// result = tempResult.toArray(new String[tempResult.size()]);

		return result;
	}

	public String[] getMsgSenderReceiverInfo(String msgId, String from, String to) throws SQLException {
		String sql = new String();
		List<String> tempResult = new ArrayList<String>();
		String[] result = new String[11];

		sql = "select * from SenderReceiver_Info where MSGID = ? and Sender = ? and Receiver = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, msgId); // PDU
		preparedStmt.setString(2, to); // PDU
		preparedStmt.setString(3, from); // PDU
		resultSet = preparedStmt.executeQuery();
		// System.out.println("insert complete");

		while (resultSet.next()) {
			for(int i=0; i<11 ; i++)
				result[i] = resultSet.getString(i+1);
			//tempResult.add(resultSet.getString("PDU"));
		}

		//result = tempResult.toArray(new String[tempResult.size()]);

		return result;
	}
	
	public String getSenderFromReceiverAndContentLocation(String receiver, String location) throws SQLException{
		
		String sql = new String();
		List<String> tempResult = new ArrayList<String>();
		String[] result = new String[3]();

		sql = "select Sender, SenderDeliveryAllowed, Message_Info.MSGID from SenderReceiver_Info inner join Message_Info " +
				"on Message_Info.MSGID = SenderReceiver_Info.MSGID " +
				"where Receiver = ? and ContentLocation = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, receiver); // PDU
		preparedStmt.setString(2, location); // PDU
		
		resultSet = preparedStmt.executeQuery();
		// System.out.println("insert complete");

		result[0] = resultSet.getString("MSGID");
		result[1] = resultSet.getString("Sender");
		result[2] = resultSet.getString("SenderDeliveryAllowed");
		
		return result;
	}
	public String GetDeliveryAllowed(String to, String msgId) throws SQLException
	{
		String sql = new String();
		List<String> tempResult = new ArrayList<String>();
		String result = new String();

		sql = "select SenderDeliveryAllowed from SenderReceiver_Info" +
					"where Receiver = ? and MSGID = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, to); // PDU
		preparedStmt.setString(2, msgId); // PDU
		
		resultSet = preparedStmt.executeQuery();
		// System.out.println("insert complete");

		result = resultSet.getString("SenderDeliveryAllowed");
		
		return result;
		
	}
	
	public String GetOriginator(String msgId, String target) throws SQLException
	{
		String sql = "select Sender from SenderReceiver_Info where MSGID = ? and Receiver = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, msgId);
		preparedStmt.setString(2, target);
		resultSet = preparedStmt.executeQuery();
		
		String result = resultSet.getString("Sender");
		
		return result;
	}
}